This map has multipe layers: one for potential customers (colorful) and others for areas we already have covered (dark, hiding the colorful layer below). By stacking these layers, we can see expansion opportunities: areas with potential customers where we haven't sold yet.
To toggle the layers, use the layers button in the upper left corner.
With Sales data on top (in black) hiding the potential customer layer below (colorful), what remains are expansion opportunities: areas of high potential customer density where we have yet to sell many products.
Clicking on an agent shows the agent's info, and clicking elsewhere on the map gives a google maps pin of that location.
mymap #show map
# Population Data:
# Energy Access Explorer
# https://www.energyaccessexplorer.org/tool/s/
# --> Tool
# --> Country: Kenya
# --> Geography Kenya
# --> Expansion of Clean Energy Markets
# --> click menu (top right)
# * remove mini-grids
# * remove lower bound on distance to distribution lines
# * remove lower bound on poverty
#
# multi-criteria analysis described here:
# https://files.wri.org/d8/s3fs-public/energy-access-explorer-data-and-methods.pdf
# sales_minimum_per_agent = 50 # show agents with at least this many sales
# sql2 = f"""
# SELECT base.angaza_id as agent,
# base.country,
# base.latitude ::DECIMAL(6,4) as base_lat,
# base.longitude::DECIMAL(7,4) as base_long,
# COUNT(accounts.angaza_id) as count_sales
# FROM kazi_prod.fse_base_location as base
# LEFT JOIN easybuy.accounts_final_pdt AS accounts
# ON base.angaza_id = accounts.registering_user_angaza_id
# WHERE accounts.registration_date_utc::DATETIME > GETDATE() - INTERVAL '{sales_interval}'
# -- todo
# # todo: active agents filter
# AND base.country = '{country.upper()}'
# GROUP BY agent, base.country, base_lat, base_long
# HAVING count_sales >= '{sales_minimum_per_agent}'
# LIMIT 100000
# """
con = con
sql2 = f""" -- this query occasionally returns zero rows, proximal problem seems to be in db
WITH agent_status_pdt AS (SELECT country,
responsible_user_angaza_id,
area_derived as area,
MAX(registration_date_utc) as last_sale
FROM easybuy.accounts_final_pdt as accounts
WHERE country = '{country}'
GROUP BY 1,2,3),
agents as(
SELECT base.angaza_id as agent,
base.country,
base.latitude ::DECIMAL(6,2) AS base_lat,
base.longitude::DECIMAL(7,2) AS base_long,
base_lat || base_long as lat_long_string,
ROW_NUMBER() OVER (PARTITION BY lat_long_string ORDER BY lat_long_string) as row_num,
agent_status_pdt.area as area,
users_all.first_name || ' ' || users_all.last_name as name,
users_all.primary_phone as phone
FROM easybuy.users AS users_all
LEFT JOIN kazi_prod.fse_base_location as base
ON users_all.angaza_id = base.angaza_id
LEFT JOIN agent_status_pdt
ON users_all.angaza_id = agent_status_pdt.responsible_user_angaza_id
WHERE DATEDIFF('DAYS',last_sale, CURRENT_DATE) < 60 -- is_active_agent
AND agent IS NOT NULL
AND agent_status_pdt.country = '{country}'
)
SELECT * from agents
WHERE row_num = 1 -- gps points hidden below other gps points are invisible and slow down / cause folium map to hang
"""
df_base2 = pd.read_sql(sql2, con)
df_base2.set_index('agent', inplace=True)
print("\n\n\n# of agents: ", df_base2.shape[0], "\n\n\n")
df_base2.head(2)
# of agents: 5730
| country | base_lat | base_long | lat_long_string | row_num | area | name | phone | |
|---|---|---|---|---|---|---|---|---|
| agent | ||||||||
| US084361 | KENYA | -0.03 | 34.02 | -0.0334.02 | 1 | Bondo | Auma Jane | +254796716660 |
| US084247 | KENYA | -0.08 | 34.23 | -0.0834.23 | 1 | Bondo | anne ongaro | +254759626562 |
# Create a raster with 5km x 5km cells
res = 5000 # Resolution in meters
cutoff_sales_num = 50
improvements
agreed:
dan's ideas:
check if all agents sell all products
subcounties and sub-sub counties: